package cn.zj.util;
import cn.zj.configuration.Constants;
import cn.zj.testScript.TestSuiteByExcel;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.regex.REUtil;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.net.URL;

//本类实现为扩展名为.xlsx的excel文件操作
public class ExcelUtil {
    private static XSSFSheet ExcelWSheet;
    private static XSSFWorkbook ExcelWBook;
    private static XSSFCell Cell;
    private static XSSFRow row;
    //设定要操作的excel文件路径和excel文件中的sheet名称
    public static void setExcelFile(InputStream inputStream, String SheetName) throws Exception{
        InputStream ExcelFile;
        try {
            ExcelFile = inputStream;
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
        }catch (Exception e){
            throw (e);
        }
    }

    public static void setExcelFile(String Path){
        FileInputStream ExcelFile;
        try {
            ExcelFile = new FileInputStream(Path);
            ExcelWBook = new XSSFWorkbook(ExcelFile);
        }catch (Exception e){
            System.out.println("excel 路径设定失败");
            e.printStackTrace();
        }
    }

    //读取excel文件指定单元格的函数，此函数目前只支持.xlsx
    public static String getCellData(String SheetName,int RowNum,int ColNum)throws Exception{
        ExcelWSheet = ExcelWBook.getSheet(SheetName);
        try {
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            //如果内容为字符串就返回String型，如果是数字就返回Num型
            if(Cell == null){
                return  "";
            }
            String CellData = Cell.getCellType() == XSSFCell.CELL_TYPE_STRING ? Cell.getStringCellValue() + "" :String.valueOf(Math.round(Cell.getNumericCellValue()));
            return  CellData;
        }catch (Exception e){
            e.printStackTrace();
            return  "";
        }
    }

    //读取excel文件指定单元格的函数，此函数目前只支持.xlsx
    public static  String getCellData(int RowNum,int ColNum) throws Exception{
        try {
            Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            //如果内容为字符串就返回String型，如果是数字就返回Num型
            String CellData = Cell.getCellType() == XSSFCell.CELL_TYPE_STRING ? Cell.getStringCellValue() + "" :String.valueOf(Math.round(Cell.getNumericCellValue()));

            return  CellData;
        }catch (Exception e){
            e.printStackTrace();
            return  "";
        }

    }

    //获取excel指定sheet中数据的总行数
    public static int getRowCount(String SheetName){
        ExcelWSheet = ExcelWBook.getSheet(SheetName);
        int number = ExcelWSheet.getLastRowNum();
        return number;
    }


    //在excel指定sheet中，获取第一次包含指定测试用例序号文字的行号
    public static int getFirstRowContainsTestCaseID(String sheetName,String testCaseName,int colNum) throws Exception{
        int i;
        ExcelWSheet = ExcelWBook.getSheet(sheetName);
        int rowCount = ExcelUtil.getRowCount(sheetName);

        for(i = 0;i<rowCount;i++){
            if(ExcelUtil.getCellData(sheetName,i,colNum).equalsIgnoreCase(testCaseName)){
                //如果包含，则退出循环，并返回行号
                break;
            }

        }
        return i;
    }

    //获取指定sheet中某个测试用例步骤的个数
    public static int getTestCaseLastStepRow(String SheetName,String testCaseID,int testCaseStartRowNumber) throws Exception{
        try {
            ExcelWSheet = ExcelWBook.getSheet(SheetName);

            //从包含指定用例序号的第一行开始逐行便利，直到某一行不出现
            for (int i = testCaseStartRowNumber; i < ExcelUtil.getRowCount(SheetName) - 1; i++) {
                if (!testCaseID.equalsIgnoreCase(ExcelUtil.getCellData(SheetName, i, Constants.Col_TestCaseID))) {
                    int number = i;
                    return number;
                }
            }
            int number = ExcelWSheet.getLastRowNum() + 1;
            return number;
        }catch (Exception e){
//            TestSuiteByExcel.testResult = false;
            return 0;
        }

    }

    //在excel文件的执行单元格中写入数据


    public static void setCellData(String sheetName,int rowNum,int colNum,String result){
        ExcelWSheet = ExcelWBook.getSheet(sheetName);
        try {
            row = ExcelWSheet.getRow(rowNum);
            //如果单元格为空，则返回null
            Cell = row.getCell(colNum,row.RETURN_BLANK_AS_NULL);
            if(Cell ==null){
                //当单元格对象是null，无法直接调用单元格对象，需先创建单元格
                Cell = row.createCell(colNum);
                Cell.setCellValue(result);

            }else {
                Cell.setCellValue(result);
            }
            //实例化写入excel文件的文件输出流对象

//            FileOutputStream fileOutputStream  =new FileOutputStream(Constants.path_test);
//            ExcelWBook.write(fileOutputStream);
//            fileOutputStream.flush();
//            fileOutputStream.close();
        }catch (Exception e){
//            TestSuiteByExcel.testResult = false;
            e.printStackTrace();
        }
    }
}
